﻿namespace System.Extensibility.Data.SqlServerModel.Kilimanjaro
open System
open System.Collections.Generic
open Option
type [<AbstractClass>] Type() = inherit Attribute() with
//    member r.Name with get() = Script.Create.Type(this).ToString()
    member r.Attributes = Seq.empty<Attribute>
//    override r.ToString() = Name
and Bit() = inherit Type()
and TinyInt() = inherit Type()
and SmallInt() = inherit Type()
and Int() = inherit Type()
and Money() = inherit Type()
and DateTime() = inherit Type()
and Date() = inherit Type()
and Time() = inherit Type()
and UniqueIdentifier() = inherit Type()
and Decimal(precision: int, scale: int) = inherit Type() with
    member r.Precision = precision
    member r.Scale = scale
    new() = Decimal(8, 2)
//[<DefaultValue (typeof<LengthPreset>, "Medium")>]
//type LengthPreset = Unspecified = 1 | Short = 50 | Medium = 450 | Max = 4000
and [<AbstractClass>] CharBase(l) =
    inherit Type()
    abstract MaxLength: int with get
    member r.Length = if (l > r.MaxLength) then raise (ArgumentException()) else l
and Char(l) = inherit CharBase(l) with
    static member maxLength = 8000
    static member max = Char Char.maxLength
    static member short = Char 50
    override r.MaxLength = Char.maxLength
    new() = Char 900
and NChar(l) = inherit CharBase(l) with
    static member maxLength = 8000
    static member max = NChar NChar.maxLength
    static member short = NChar 50
    override r.MaxLength = NChar.maxLength
    new() = NChar 900
and VarChar(l) = inherit CharBase(l) with
    static member maxLength = 4000
    static member max = VarChar VarChar.maxLength
    static member short = VarChar 50
    override r.MaxLength = VarChar.maxLength
    new() = VarChar 450
and NVarChar(l) = inherit CharBase (l) with
    static member maxLength = 4000
    static member max = NVarChar NVarChar.maxLength
    static member short = NVarChar 50
    override r.MaxLength = NVarChar.maxLength
    new() = NVarChar 450
and VarBinary(l) = inherit CharBase (l) with
    static member maxLength = 4000
    static member max = VarBinary VarBinary.maxLength
    static member short = VarBinary 50
    override r.MaxLength = VarBinary.maxLength
    new() = VarBinary 1
and NText() = inherit Type()
and Text() = inherit Type()
and Xml() = inherit Type()
and Image() = inherit Type()
and SysName() = inherit Type()
and [<Obsolete>] NotSupported() = inherit Type()
and [<AbstractClass>] Member (?name, ?attributes) =
    member a.Name = defaultArg name ""
    member a.Attributes = defaultArg attributes Seq.empty<Attribute>
    override a.ToString() = a.Name
and Field(name, v) =
    inherit Member (name)
    [<Core.DefaultValue>] val mutable Column: Column
//    member r.Type: Type = t
    member r.Type = r.Column.Type
    member r.Value: obj = v
and Row =
    inherit Member
    val mutable Fields: seq<Field>
    new fields = { Fields = fields }
and Column(name, t, ?isIdentity, ?allowNulls) =
    inherit Member (name)
    abstract Type: Type with get
    default r.Type with get() = t
    member a.AllowNulls = defaultArg allowNulls false
    member a.IsIdentity = defaultArg isIdentity false
    member a.DBType = a.Type.GetType().Name.ToUpper() + (if not a.AllowNulls then " NOT NULL" else "") + (if a.IsIdentity then " IDENTITY" else "")
and [<AbstractClass>] SystemColumn(n: string, t: Type, ?isIdentity) = inherit Column(n, t, ?isIdentity = isIdentity)
and IdentityColumn (?n: string) = inherit SystemColumn(defaultArg n "id", Int(), true)
    //IsDBGenerated = true
and RowGuidColumn (?name) = inherit SystemColumn(defaultArg name "g", UniqueIdentifier())
and ModifiedDateColumn() = inherit SystemColumn("ModifiedDate", DateTime())
//and Schema(n) = inherit Member(n) static member Default = Schema ("dbo")
and Schema = { Name: string; Attributes: seq<Attribute> }
    with
        static member create name = { Name = name; Attributes = Seq.empty }
        static member ``default`` = Schema.create "dbo"
and [<AbstractClass>] QueryableMember (name, ?columns, ?rows) =
    inherit Member(name)
    member a.Columns = defaultArg columns Seq.empty<Column>
    member a.Rows = defaultArg rows Seq.empty<Row>
and TableScript = { Select: string; Insert: string; Update: string; Delete: string }
and Table (name, ?columns, ?rows, ?schema, ?id, ?g, ?primaryKey, ?keys) =
    inherit QueryableMember (name, ?columns = columns, ?rows = rows)
    member r.UserColumns = r.Columns |> Seq.filter (fun p -> not (p :? SystemColumn))
    member a.Keys = defaultArg keys Seq.empty<seq<Column>>
    member a.PrimaryKey = defaultArg primaryKey Seq.empty<Column>
    member a.IdentityColumn: IdentityColumn option = id
    member a.RowGuidColumn: RowGuidColumn option = g
    //public IEnumerable<Association> Associations { get; set; }
    //public IEnumerable<Association> ForeignAssociations { get; set; }
    member a.Schema = defaultArg schema Schema.``default``
//    [<DefaultValue>] val mutable Database: Database
    member a.FullName = if a.Schema.Name <> Schema.``default``.Name then String.Format("[{0}].[{1}]", a.Schema.Name, a.Name) else String.Format("[{0}]", a.Name)
    member v.Script =
//        let useID = isSome v.IdentityColumn
//        let id = if useID then v.IdentityColumn.Value.Name else null
        let primaryKey = if Seq.isEmpty v.PrimaryKey then None else Some (v.PrimaryKey |> Seq.head).Name
//        let rowGuid = v.RowGuidColumn.Value.Name
        let modifiedDate = "ModifiedDate"
        let editables = Seq.append (v.UserColumns |> Seq.map (fun a -> a.Name)) [ modifiedDate ]
//        let readonlies = if rowGuid <> "" then Seq.append editables [ rowGuid ] else editables
        let readonlies = editables
        let fields a = (a |> Seq.fold (fun a b -> a + ", [" + b + "]") "").Remove(0, ", ".Length)
        let parameters = (readonlies |> Seq.fold (fun a b -> a + ", @" + b) "").Remove(0, ", ".Length)
        let assignings = (editables |> Seq.fold (fun a b ->  a + ", [" + b + "] = @" + b) "").Remove(0, ", ".Length)
        let name = v.FullName
//        let select = sprintf "SELECT %s FROM %s" (fields (Seq.append(toList primaryKey) readonlies)) name
        let select = sprintf "SELECT * FROM %s" name
        let insert = sprintf "INSERT INTO %s (%s) VALUES (%s)" name (fields readonlies) parameters
        let update = if isSome primaryKey then sprintf "UPDATE %s SET %s WHERE [%s] = @%s" name assignings (get primaryKey) (get primaryKey) else ""
        let delete = if isSome primaryKey then sprintf "DELETE FROM %s WHERE [%s] = @%s" name (get primaryKey) (get primaryKey) else ""
        { Select = select; Insert = insert; Update = update; Delete = delete }
    new (name, columns, schema, id, g, primaryKey, keys) = Table (name, columns, Seq.empty<Row>, schema, ?id = id, ?g = g, primaryKey = primaryKey, keys = keys)
and TableReference = { Name: string; FullName: string; PrimaryKeyName: string; PrimaryKeyType: Type }
and AssociationColumn (name: string, t, ?isIdentity, ?allowNulls) = inherit Column(name, t.PrimaryKeyType, ?isIdentity = isIdentity, ?allowNulls = allowNulls) with
    member a.ForeignTable = t
    override r.Type with get() = t.PrimaryKeyType
    new (name: string, t: Table, ?allowNulls, ?isIdentity) = AssociationColumn (name, { Name = t.Name; FullName = t.FullName; PrimaryKeyName = (t.PrimaryKey |> Seq.head).Name; PrimaryKeyType = (t.PrimaryKey |> Seq.head).Type }, ?isIdentity = isIdentity, ?allowNulls = allowNulls)
and View (name) = class inherit QueryableMember (name) end
and Database (name, ?tables, ?schemas) =
    inherit Member (name)
    member a.Tables = defaultArg tables Seq.empty<Table>
    member a.Schemas = defaultArg schemas (Seq.singleton Schema.``default``)
module Descriptor =
    module Samples =
        let f n v = Field (n, v)
        let g (a: string) = Guid a
        let d (a: string) = System.DateTime.Parse a
        let t name schema (rows: seq<Row>) (columns: seq<Column>) =
            let id = IdentityColumn (name + "ID")
            let rowGuid = RowGuidColumn()
            let modifiedDate = ModifiedDateColumn()
            let columns = Seq.concat [ seq<Column> [ id ]; columns; seq<Column> [ rowGuid; modifiedDate ] ]
            Table (name, columns, rows, schema, id, rowGuid, [ id ], [ [ id ] ])
        let fix (r: Database) =
//            let createSystemColumns (entities: seq<Table>) =
//                for r in entities do
//                    if r.PrimaryKey = null then
//                        let id = IdentityColumn (r.Name + "ID")
//                        r.Columns <- Seq.append [ id ] r.Columns
////                        r.IdentityColumn <- id
//                        r.PrimaryKey <- [ id ]
//                        r.Keys <- [ r.PrimaryKey ]
//                    let rowGuid = RowGuidColumn()
//                    let modifiedDate = ModifiedDateColumn()
//                    r.RowGuidColumn <- rowGuid
//                    r.Columns <- Seq.append r.Columns [ rowGuid; modifiedDate ]
            let assignColumnToFields (r: Table) = r.Rows |> Seq.collect (fun p -> p.Fields) |> Seq.iter (fun p -> p.Column <- r.Columns |> Seq.find (fun q -> q.Name = p.Name))
//            createSystemColumns r.Tables
            r.Tables |> Seq.iter (fun p -> assignColumnToFields p)
        let guestbook =
            let r =
                Database ("Guestbook",
                    [ t "Guestbook" Schema.``default``
                        [ Row [ f "GuestbookID" 1; f "Title" "Hello World!"; f "Text" "\"ide@\" is an IDE application idea."; f "GuestName" "a_a"; f "g" (g "415BF1E6-0527-4198-9CCC-E016B5F2E017"); f "ModifiedDate" (d "2:01:25 AM, Sunday, March 15, 2009") ] ]
                        [ Column("Title", NVarChar.short, allowNulls = true); Column("Text", NVarChar.max, allowNulls = true); Column("GuestName", NVarChar.short, allowNulls = true) ] ])
            fix r
            r
        let nameCards =
            let profession =
                t "Profession" Schema.``default``
                    [ Row [ f "ProfessionID" 1; f "Name" "Developer"; f "g" (g "64cf9d2e-f2c0-4137-90ad-537b3cb9b287"); f "ModifiedDate" (d "4/8/2009 9:56:18 PM") ];
                        Row [ f "ProfessionID" 2; f "Name" "Human Resource"; f "g" (g "b0dd64af-fe64-4a1b-b588-70454925abd1"); f "ModifiedDate" (d "4/8/2009 9:56:18 PM") ];
                        Row [ f "ProfessionID" 3; f "Name" "Project Manager"; f "g" (g "6e5cd9a0-dab4-4fec-bc6e-aac83c73923e"); f "ModifiedDate" (d "4/8/2009 9:56:18 PM") ];
                        Row [ f "ProfessionID" 4; f "Name" "Architecture Designer"; f "g" (g "2db2877b-3eb4-4767-bb55-29fb51b2f63f"); f "ModifiedDate" (d "4/8/2009 9:56:18 PM") ] ]
                    [ Column("Name", NVarChar 50, allowNulls = true) ]
            let r =
                Database ("NameCards",
                    Seq.cache [
                        t "Person" Schema.``default`` [ Row [ Field ("PersonID", 1); Field ("Name", "Somebody"); Field ("Age", 32); Field ("JobID", 1); Field ("g", Guid "1c3c997c-15ed-4523-9be5-818c4046a932"); Field ("ModifiedDate", System.DateTime.Parse "4/8/2009 9:56:18 PM") ] ]
                            [ Column( "Name", NVarChar(50), allowNulls = true);
                                Column("Age", Int(), allowNulls = true);
                                AssociationColumn ("JobID", profession, allowNulls = true) ];
                        profession ])
            fix r
            r
        let adventureWorks =
            let t name schema (rows: seq<Row>) (columns: seq<Column>) =
                let id = IdentityColumn (name + "ID")
                let rowGuid = RowGuidColumn "rowguid"
                let modifiedDate = ModifiedDateColumn()
                let columns = Seq.concat [ seq<Column> [ id ]; columns; seq<Column> [ rowGuid; modifiedDate ] ]
                Table (name, columns, rows, schema, id, rowGuid, [ id ], [ [ id ] ])
            let production, sales = Schema.create "Production", Schema.create "Sales"
            let subcategory = t "ProductSubcategory" production Seq.empty [ Column("Name", NVarChar(50), allowNulls = true) ]
            let product =
                t "Product" production Seq.empty
                    [ Column("Name", NVarChar(50), allowNulls = true);
                        Column("ProductNumber", NVarChar(25), allowNulls = true);
                        Column("MakeFlag", Bit());
                        Column("FinishedGoodsFlag", Bit());
                        Column("Color", NVarChar(15), allowNulls = true);
                        Column("SafetyStockLevel", SmallInt());
                        Column("ReorderPoint", SmallInt());
                        Column("StandardCost", Money());
                        Column("ListPrice", Money());
                        Column("Size", NVarChar(5), allowNulls = true);
                        Column("SizeUnitMeasureCode", NChar(3), allowNulls = true);
                        Column("WeightUnitMeasureCode", NChar(3), allowNulls = true);
                        Column("Weight", Decimal(), allowNulls = true);
                        Column("DaysToManufacture", Int());
                        Column("ProductLine", NChar(2), allowNulls = true);
                        Column("Class", NChar(2), allowNulls = true);
                        Column("Style", NChar(2), allowNulls = true);
                        AssociationColumn ("ProductSubcategoryID", subcategory, allowNulls = true);
                        Column("SellStartDate", DateTime());
                        Column( "SellEndDate", DateTime(), allowNulls = true);
                        Column("DiscontinuedDate", DateTime(), allowNulls = true) ]
            let inventory = t "ProductInventory" production Seq.empty [ AssociationColumn ("ProductID", product); Column("Shelf", NVarChar(10), allowNulls = true); Column("Bin", TinyInt()); Column("Quantity", SmallInt()) ]
            let cartItem = t "ShoppingCartItem" sales Seq.empty [ Column("ShoppingCartID", Int()); Column("Quantity", Int()); AssociationColumn ("ProductID", product); Column("DateCreated", DateTime()) ]
            let r = Database ("AdventureWorks2008", ([inventory; product; subcategory; cartItem ] |> Seq.sortBy (fun p -> p.Name)), [ production; sales ])
            fix r
            r
module Test =
    open Descriptor.Samples
    let guestbookTest _ =
        let r = guestbook
        assert (r.Tables |> Seq.length = 1)
        assert (r.Tables |> Seq.map (fun p -> p.Name) |> String.concat ", " = "Guestbook")
        assert (r.Tables |> Seq.map (fun p -> p.FullName) |> String.concat ", " = "[Guestbook]")
        assert ((r.Tables |> Seq.head).Columns |> Seq.map (fun p -> p.Name) |> String.concat ", " = "GuestbookID, Title, Text, GuestName, g, ModifiedDate")
    let nameCardsTest _ =
        let r = nameCards
        assert (r.Tables |> Seq.length = 2)
        assert (r.Tables |> Seq.map (fun p -> p.Name) |> String.concat ", " = "Person, Profession")
        assert (r.Tables |> Seq.map (fun p -> p.FullName) |> String.concat ", " = "[Person], [Profession]")
        assert ((r.Tables |> Seq.head).Columns |> Seq.map (fun p -> p.Name) |> String.concat ", " = "PersonID, Name, Age, JobID, g, ModifiedDate")
        /// Associations
//        assert (r.Tables |> Seq.find (fun p -> p.Name = "Profession") = (((r.Tables |> Seq.find (fun p -> p.Name = "Person")).Columns |> Seq.find (fun p -> p.Name = "JobID")) :?> AssociationColumn).Table)
    let adventureWorksDeclarationTest _ =
        assert (adventureWorks.Tables |> Seq.length = 4)
        assert (adventureWorks.Tables |> Seq.map (fun p -> p.Name) |> String.concat ", " = "Product, ProductInventory, ProductSubcategory, ShoppingCartItem")
        assert (adventureWorks.Tables |> Seq.map (fun p -> p.FullName) |> String.concat ", " = "[Production].[Product], [Production].[ProductInventory], [Production].[ProductSubcategory], [Sales].[ShoppingCartItem]")
        assert ((adventureWorks.Tables |> Seq.skip 1 |> Seq.head).Columns |> Seq.map (fun p -> p.Name) |> String.concat ", " = "ProductInventoryID, ProductID, Shelf, Bin, Quantity, rowguid, ModifiedDate")
        /// Associations
//        assert (adventureWorks.Tables |> Seq.find (fun p -> p.Name = "ProductSubcategory") = ((adventureWorks.Tables |> Seq.find (fun p -> p.Name = "Product")).Columns |> Seq.find (fun p -> p.Name = "ProductSubcategoryID") :?> AssociationColumn).Table)
//        assert (adventureWorks.Tables |> Seq.find (fun p -> p.Name = "Product") = ((adventureWorks.Tables |> Seq.find (fun p -> p.Name = "ProductInventory")).Columns |> Seq.find (fun p -> p.Name = "ProductID") :?> AssociationColumn).Table)